package cn.doitedu.sql;

import org.apache.flink.streaming.api.CheckpointingMode;
import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;
import org.apache.flink.table.api.bridge.java.StreamTableEnvironment;

public class Demo11_FunctionsTest {

    public static void main(String[] args) {

        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
        env.enableCheckpointing(5000, CheckpointingMode.EXACTLY_ONCE);
        env.getCheckpointConfig().setCheckpointStorage("file:///d:/ckpt");

        env.setParallelism(1);

        StreamTableEnvironment tenv = StreamTableEnvironment.create(env);



        //tenv.executeSql("  select trim(BOTH '-' from '--xxx-')  ").print();


        //tenv.executeSql("  select '-' || trim('   aaa    ')  || '-' ").print();


        //tenv.executeSql("  select substring('123456',2,3) ").print();


        // url: /aa/bb/cc/dd.html?a=3&b=4
        // url: /aa/bc/x.html?a=3&b=4
        //tenv.executeSql("select  regexp_extract('/aa/bb/cc/dd.html?a=3&b=4', '^(/.*/).*' , 1 ) ").print();
        //tenv.executeSql("select  regexp_extract('/aa/bb/cc/dd.html?a=3&b=4', '^/.*(/.*)' , 1 ) ").print();
        //tenv.executeSql("select  regexp('/aa/bb/cc/dd.html?a=3&b=4', '^/.*/.*') ").print();

        //tenv.executeSql("select split_index('aa,bb,cc,dd', ',', 2 )").print();


        //tenv.executeSql("select str_to_map('a:3,b:4,ww:5', ',' , ':' )").print();
        //tenv.executeSql("select LOCALTIME ,LOCALTIMESTAMP ,CURRENT_TIME ,CURRENT_DATE ,CURRENT_TIMESTAMP ,NOW()").print();

        //tenv.executeSql("select EXTRACT(MONTH FROM DATE '2006-06-05') ,EXTRACT(MONTH FROM TO_DATE('2006-06-05')),EXTRACT(HOUR FROM TIME '09:19:20') , EXTRACT(MINUTE FROM TIMESTAMP '2024-04-15 09:19:20')  ").print();

        //tenv.executeSql("select  CEIL(TIME '12:44:31' TO MINUTE) ,  CEIL(TIME '12:44:31' TO HOUR)  ,FLOOR(TIME '12:46:31' TO HOUR)").print();


        //tenv.executeSql("select  TIMESTAMPADD(HOUR, 1, TIMESTAMP '2024-04-15 12:30:00') , TIMESTAMPADD(DAY, 1, TIMESTAMP '2024-04-15 12:30:00'),TIMESTAMPADD(DAY, 1, DATE '2024-04-15') ").print();




        tenv.getConfig().set("table.local-time-zone","GMT+00:00");
        // 把长整数转成timestamp，都会参考环境中的时区（TimeZone）
        //tenv.executeSql("select TO_TIMESTAMP_LTZ(1713145232000, 3)  ,  TO_TIMESTAMP('2024-04-15 09:40:30.100')  ").print();
        //tenv.executeSql("select  FROM_UNIXTIME(1713145232)").print();




        //tenv.executeSql("select json_string( str_to_map('a:3,b:4,ww:5', ',' , ':' ) ) ").print();


        // {"name":"zs", "age":18, "friends":[{"name":"liuyifei","job":"actress"},{"name":"huangbo","job":"hunhun"}]}
        tenv.executeSql("select  json_value('{\"name\":\"zs\", \"age\":18, \"friends\":[{\"name\":\"liuyifei\",\"job\":\"actress\"},{\"name\":\"huangbo\",\"job\":\"hunhun\"}]}',  '$.friends[0].job')").print();



    }

}
